package Classes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * 
 * @author Karla Barber
 * General Customers Class creation
 *
 */
public class Customers 
{
	//===============================Properties=======================================
	// start Karla Barber properties
	private int customerId;
	private String custFirstName;
	private String custLastName;
	private String custAddress;
	private String custCity;
	private String custProvince;
	private String custPostalCode;
	private String custCountry;
	private String custHomePhone;
	private String custBusPhone;
	private String custEmail;
	private int agentId;
	private String Inactive;
	
	// end Karla Barber properties
	
	//===============================Constructors=======================================
	
	//===============================Getters and Setters================================
	// start Karla Barber getters and setters
	public int getCustomerId() 
	{
		return customerId;
	}
	public void setCustomerId(int customerId) 
	{
		this.customerId = customerId;
	}
	
	
	public String getCustFirstName() 
	{
		return custFirstName;
	}
	public void setCustFirstName(String custFirstName) 
	{
		this.custFirstName = custFirstName;
	}
	
	
	public String getCustLastName() 
	{
		return custLastName;
	}
	public void setCustLastName(String custLastName) 
	{
		this.custLastName = custLastName;
	}
	
	
	public String getCustAddress() 
	{
		return custAddress;
	}
	public void setCustAddress(String custAddress) 
	{
		this.custAddress = custAddress;
	}
	
	
	public String getCustCity() 
	{
		return custCity;
	}
	public void setCustCity(String custCity) 
	{
		this.custCity = custCity;
	}
	
	
	public String getCustProvince() 
	{
		return custProvince;
	}
	public void setCustProvince(String custProvince) 
	{
		this.custProvince = custProvince;
	}
	
	
	public String getCustPostalCode() 
	{
		return custPostalCode;
	}
	public void setCustPostalCode(String custPostalCode) 
	{
		this.custPostalCode = custPostalCode;
	}
	
	
	public String getCustCountry() 
	{
		return custCountry;
	}
	public void setCustCountry(String custCountry) 
	{
		this.custCountry = custCountry;
	}
	
	
	public String getCustHomePhone() 
	{
		return custHomePhone;
	}
	public void setCustHomePhone(String custHomePhone) 
	{
		this.custHomePhone = custHomePhone;
	}
	
	
	public String getCustBusPhone() 
	{
		return custBusPhone;
	}
	public void setCustBusPhone(String custBusPhone) 
	{
		this.custBusPhone = custBusPhone;
	}
	
	
	public String getCustEmail() 
	{
		return custEmail;
	}
	public void setCustEmail(String custEmail) 
	{
		this.custEmail = custEmail;
	}
	
	
	public int getAgentId() 
	{
		return agentId;
	}
	public void setAgentId(int agentId) 
	{
		this.agentId = agentId;
	}
	public void setInactive(String inactive) {
		Inactive = inactive;
	}
	public String getInactive() {
		return Inactive;
	}
	@Override
	public String toString() {
		return custLastName + ", "	+  custFirstName;
	}
	
	
	// end Karla Barber getters and setters
	
	//===============================Methods============================================
    //this method connects to the DB
    public Connection connectToDb()
    {
            try 
            {       
                    //load the driver for oracle
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    
                    //establish a connection to the database
                    //first string is location of the database (url)
                    //second string is user name
                    //third string is password
                    Connection agentConnection =  DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g", "ictoosd", "ictoosd");
                    return agentConnection;
            } 
            catch (ClassNotFoundException e) 
            {
                    e.printStackTrace();
                    Connection noConnectionMade = null;
                    return noConnectionMade;
            } catch (SQLException sqle) 
            {
                    sqle.printStackTrace();
                    Connection noConnectionMade = null;
                    return noConnectionMade;
            }       
    }
    
    //this method receives a sql statement and returns the result set in a vector
    public Vector<String> loadCustVector(String sql) 
    {
            Vector v;
            v = new Vector();
            try 
            {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
                    //String lsql = "select * from ICTOOSD.CUSTOMERS ORDER BY CUSTLASTNAME";
                    //String lsql = "select * from ICTOOSD.CUSTOMERS WHERE CUSTOMERID = 135";
                    Statement stmt = dbConn.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next())
                    {
                            Customers c = new Customers();
                            c.setCustomerId(rs.getInt("CUSTOMERID"));
                            c.setCustFirstName(rs.getString("CUSTFIRSTNAME"));
                            c.setCustLastName(rs.getString("CUSTLASTNAME"));
                            c.setCustAddress(rs.getString("CUSTADDRESS"));
                            c.setCustCity(rs.getString("CUSTCITY"));
                            c.setCustProvince(rs.getString("CUSTPROV"));
                            c.setCustPostalCode(rs.getString("CUSTPOSTAL"));
                            c.setCustCountry(rs.getString("CUSTCOUNTRY"));
                            c.setCustHomePhone(rs.getString("CUSTHOMEPHONE"));
                            c.setCustBusPhone(rs.getString("CUSTBUSPHONE"));
                            c.setCustEmail(rs.getString("CUSTEMAIL"));
                            c.setAgentId(rs.getInt("AGENTID"));
                            String tmp = rs.getString("INACTIVE");
                            //System.out.println("Inactive Flag: " + tmp);
                            if ("1".equalsIgnoreCase(rs.getString("INACTIVE")))
                            {
                            	c.setInactive("true");
                            }
                            else
                            {
                            	c.setInactive("false");
                            }
                            v.addElement(c);
                    }
                    dbConn.close();
            } 
            catch (SQLException e) 
            {
                    e.printStackTrace();
            } catch (ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return v;
    }
    
    //this method writes customer changes to the database
    public void updateCustomer()
    {
        try 
        {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
                String lsql = "update ICTOOSD.CUSTOMERS " +
                			  "set CUSTFIRSTNAME = '" + custFirstName + "', " +
                			      "CUSTLASTNAME = '" + custLastName + "', " +
                			      "CUSTADDRESS = '" + custAddress + "', " +
                			      "CUSTCITY = '" + custCity + "', " +
                			      "CUSTPROV = '" + custProvince + "', " +
                			      "CUSTPOSTAL = '" + custPostalCode + "', " +
                			      "CUSTCOUNTRY = '" + custCountry + "', " +
                			      "CUSTHOMEPHONE = '" + custHomePhone + "', " +
                			      "CUSTBUSPHONE = '" + custBusPhone + "', " +
                			      "CUSTEMAIL = '" + custEmail + "', " +
                			      "AGENTID = '" + agentId + "', " +
                			      "INACTIVE = '" + Inactive + "' " +
                			  "where CUSTOMERID = " + customerId;
                			      
                Statement stmt = dbConn.createStatement();
                stmt.execute(lsql);
                dbConn.commit();
                dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }

	//this function returns the next sequential id for a new customer added to the database
	public int getNextCustomerId()
	{
		int newCustomerId = 0;
		
		//connect to database
		Connection dbConn = connectToDb();
		
		if (dbConn != null)
		{
			try {
				//Create a new Statement
				Statement stmnt = dbConn.createStatement();
				ResultSet rs = stmnt.executeQuery("select max(customerid) from ictoosd.customers");
				
				
				while (rs.next())
				{
					//grab the last number and add one to it to get next number
					newCustomerId = rs.getInt(1) + 1;
				}
				dbConn.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}

		}
		else
		{
			System.out.println("No SQL connection was made.");
		}
		
		return newCustomerId;
	}

	//this method adds new customers to the database
	public void addCustomer()
    {
        try 
        {
        	int custId = getNextCustomerId();
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
            
            String insertAgentId;
            if (agentId == 0) {
            	insertAgentId = "null";
	        }
            else{
            	insertAgentId = Integer.toString(agentId);            	
            }
            
            String lsql = "insert into ICTOOSD.CUSTOMERS" +
            			  "(CUSTFIRSTNAME, CUSTLASTNAME, CUSTADDRESS, CUSTCITY, CUSTPROV, CUSTPOSTAL, " +
            			   "CUSTCOUNTRY, CUSTHOMEPHONE, CUSTBUSPHONE, CUSTEMAIL, AGENTID, INACTIVE, CUSTOMERID) " +
            			  "Values('" + custFirstName + "', '" + custLastName + "', '" + custAddress + "', '" +
            			  	custCity + "', '" + custProvince + "', '" + custPostalCode + "', '" + custCountry + "', '" + 
            			  	custHomePhone +  "', '" + custBusPhone  + "', '" + custEmail + "', " + insertAgentId + ", '" +
            			  	Inactive  + "', " + custId + ")";
            
	        Statement addStmnt = dbConn.createStatement();
	        
	        int rowsChanged = addStmnt.executeUpdate(lsql);
	        
	        if (rowsChanged == 0)
	        {
	        	System.out.println("update failed");
	        }
	        else
	        {
	            String lsql2 = "insert into ICTOOSD.CUSTOMERLOGIN" +
  			  				  "(CUSTOMERID, USERNAME, PASSWORD) " +
  			  				  "Values(?,?,?)";
      			      
			  PreparedStatement addStmnt2 = dbConn.prepareStatement(lsql2);
			     
			  addStmnt2.setString(1, Integer.toString(custId));
			  addStmnt2.setString(2, custFirstName.substring(0, 4) + "" + custId);
			  addStmnt2.setString(3, "1234");
			  
			  int rowsChanged2 = addStmnt2.executeUpdate();
	        	
		        if (rowsChanged2 == 0)
		        {
		        	System.out.println("update failed");
		        	dbConn.rollback();
		        }
		        else
		        {
		        	System.out.println("updated " + rowsChanged + " row(s)");
		        }
	        }
	        dbConn.commit();
	        dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }

	//this method sets customers to inactive
    public void deleteCustomer()
    {
        try 
        {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
                String lsql = "update ICTOOSD.CUSTOMERS " +
                			  "set INACTIVE = '1' " +
                			  "where CUSTOMERID = " + customerId;
                			      
                Statement stmt = dbConn.createStatement();
                stmt.execute(lsql);
                dbConn.commit();
                dbConn.close();
        }
        catch (SQLException e) 
        {
                e.printStackTrace();
        } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
    }
}
